package com.smx.dao;

import com.smx.model.entity.BorrowBooks;
import com.smx.model.vo.BooksVo;
import com.smx.model.vo.BorrowBooksVo;
import com.smx.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName BookDao
 * @Description TODO
 * @Author moses
 * @Date 2021/5/10
 **/
public class BookDao {

    private static final String SQL_GETALL = "select b.id as id,b.name as name,b.author as author,b.library_id as libraryId," +
            "l.name as libraryName,l.description as libraryDescription, b.sort_id as sortId,s.name as sortName,s.description as sortDescription," +
            "b.position as position,b.status as status,b.description as description from books b inner join library l on b.library_id=l.id " +
            "inner join book_sort s on b.sort_id=s.id";

    private static final String SQL_GETALL_BY_PAGE = "select b.id as id,b.name as name,b.author as author,b.library_id as libraryId," +
            "l.name as libraryName,l.description as libraryDescription, b.sort_id as sortId,s.name as sortName,s.description as sortDescription," +
            "b.position as position,b.status as status,b.description as description from books b inner join library l on b.library_id=l.id " +
            "inner join book_sort s on b.sort_id=s.id limit ?,?";

    private static final String SQL_GETSTOREID_BY_USERID = "select book_id as bookId from store_books where user_id =? ";

    private static final String SQL_GETALLCOUNT = "select count(*) as count from books";

    private static final String SQL_STORE_BOOK_BY_USERID = "insert into store_books (book_id,user_id) values (?,?)";

    private static final String SQL_CANCEL_STORE_BOOK_BY_USERID_AND_BOOKID = "delete from store_books where book_id=? and user_id=?";

    private static final String SQL_GET_BORROW_BOOKS_BY_USERID = "select a.*,b.name as bookName from borrow_books a inner join books b on b.id=a.book_id where user_id =? and return_date is null limit ?,?";

    private static final String SQL_GET_BORROW_BOOKS_COUNT_BY_USERID = "select count(*) count from borrow_books where user_id=? and return_date is null";

    private static final String SQL_GET_HISTORY_BORROW_BOOKS_COUNT_BY_USERID = "select count(*) count from borrow_books where user_id=? and return_date is not null";

    private static final String SQL_INSERT_BORROW_BOOK = "insert into borrow_books (book_id,borrow_date,end_date,user_id) values " +
            "(?,?,?,?)";

    private static final String SQL_GET_ALL_BORROW_BOOks_BY_USERID = "select a.*,b.name as bookName from borrow_books a inner join books b on b.id=a.book_id where user_id =? and return_date is null";

    private static final String SQL_GET_HISTORY_BORROW_BOOKS_BY_USERID="select a.*,b.name as bookName from borrow_books a inner join books b on b.id=a.book_id where user_id =? and return_date is not null limit ?,?";

    private static final String SQL_REMOVE_BORROW_BOOK_BY_USERID_AND_BOOKID = "delete from borrow_books where book_id=? and user_id=?";

    private static final String SQL_RETURN_BORROW_BOOK_BY_USERID_AND_BOOKID = "update borrow_books set return_date=? ,illegal =? where book_id=? and user_id=?";


    public static List<BooksVo> getAll() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        ResultSet rs = null;
        List<BooksVo> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_GETALL);
            rs = state.executeQuery();
            while (rs.next()) {
                BooksVo booksVo = new BooksVo();
                booksVo.setId(rs.getInt("id"));
                booksVo.setName(rs.getString("name"));
                booksVo.setAuthor(rs.getString("author"));
                booksVo.setLibraryId(rs.getInt("libraryId"));
                booksVo.setLibraryName(rs.getString("libraryName"));
                booksVo.setLibraryDescription(rs.getString("libraryDescription"));
                booksVo.setSortId(rs.getInt("sortId"));
                booksVo.setSortName(rs.getString("sortName"));
                booksVo.setSortDescription(rs.getString("sortDescription"));
                booksVo.setPosition(rs.getString("position"));
                booksVo.setStatus(rs.getInt("status"));
                booksVo.setDescription(rs.getString("description"));
                list.add(booksVo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    public static List<BooksVo> getAllByPage(int begin,int end) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        ResultSet rs = null;
        List<BooksVo> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_GETALL_BY_PAGE);
            state.setInt(1, begin);
            state.setInt(2, end);
            rs = state.executeQuery();
            while (rs.next()) {
                BooksVo booksVo = new BooksVo();
                booksVo.setId(rs.getInt("id"));
                booksVo.setName(rs.getString("name"));
                booksVo.setAuthor(rs.getString("author"));
                booksVo.setLibraryId(rs.getInt("libraryId"));
                booksVo.setLibraryName(rs.getString("libraryName"));
                booksVo.setLibraryDescription(rs.getString("libraryDescription"));
                booksVo.setSortId(rs.getInt("sortId"));
                booksVo.setSortName(rs.getString("sortName"));
                booksVo.setSortDescription(rs.getString("sortDescription"));
                booksVo.setPosition(rs.getString("position"));
                booksVo.setStatus(rs.getInt("status"));
                booksVo.setDescription(rs.getString("description"));
                list.add(booksVo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    public static List<Integer> getAllStoreBookIdByUserId(int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        ResultSet rs = null;
        List<Integer> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_GETSTOREID_BY_USERID);
            state.setInt(1, userId);

            rs = state.executeQuery();
            while (rs.next()) {
                list.add(rs.getInt("bookId"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    public static int getAllCount() {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_GETALLCOUNT);
            rs = state.executeQuery();
            if (rs.next()) {
                return rs.getInt("count");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return 0;
    }

    public static int addStoreId(int bookId,int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        try {
            state=conn.prepareStatement(SQL_STORE_BOOK_BY_USERID);
            state.setInt(1, bookId);
            state.setInt(2, userId);
            int i = state.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return 0;
    }

    public static int removeStore(int bookId, int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        try {
            state=conn.prepareStatement(SQL_CANCEL_STORE_BOOK_BY_USERID_AND_BOOKID);
            state.setInt(1, bookId);
            state.setInt(2, userId);
            int i = state.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return 0;
    }

    public static List<BorrowBooksVo> getBorrowBooksByUserId(int userId, int begin, int end) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<BorrowBooksVo> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_GET_BORROW_BOOKS_BY_USERID);
            state.setInt(1, userId);
            state.setInt(2, begin);
            state.setInt(3, end);
            rs = state.executeQuery();
            while (rs.next()) {
                BorrowBooksVo borrowBooks = new BorrowBooksVo();
                borrowBooks.setId(rs.getInt("id"));
                borrowBooks.setBookId(rs.getInt("book_id"));
                borrowBooks.setBorrowDate(rs.getDate("borrow_date"));
                borrowBooks.setEndDate(rs.getDate("end_date"));
                borrowBooks.setReturnDate(rs.getDate("return_date"));
                borrowBooks.setIllegal(rs.getString("illegal"));
                borrowBooks.setUserId(userId);
                borrowBooks.setBookName(rs.getString("bookName"));
                list.add(borrowBooks);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    public static List<BorrowBooksVo> getAllBorrowBooksByUserId(int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<BorrowBooksVo> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_GET_ALL_BORROW_BOOks_BY_USERID);
            state.setInt(1, userId);
            rs = state.executeQuery();
            while (rs.next()) {
                BorrowBooksVo borrowBooks = new BorrowBooksVo();
                borrowBooks.setId(rs.getInt("id"));
                borrowBooks.setBookId(rs.getInt("book_id"));
                borrowBooks.setBorrowDate(rs.getDate("borrow_date"));
                borrowBooks.setEndDate(rs.getDate("end_date"));
                borrowBooks.setReturnDate(rs.getDate("return_date"));
                borrowBooks.setIllegal(rs.getString("illegal"));
                borrowBooks.setUserId(userId);
                borrowBooks.setBookName(rs.getString("bookName"));
                list.add(borrowBooks);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    public static List<BorrowBooksVo> getHistoryBorrowBooksByUserIdPage(int userId, int begin, int end) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        List<BorrowBooksVo> list = new ArrayList<>();
        try {
            state = conn.prepareStatement(SQL_GET_HISTORY_BORROW_BOOKS_BY_USERID);
            state.setInt(1, userId);
            state.setInt(2, begin);
            state.setInt(3, end);
            rs = state.executeQuery();
            while (rs.next()) {
                BorrowBooksVo borrowBooks = new BorrowBooksVo();
                borrowBooks.setId(rs.getInt("id"));
                borrowBooks.setBookId(rs.getInt("book_id"));
                borrowBooks.setBorrowDate(rs.getDate("borrow_date"));
                borrowBooks.setEndDate(rs.getDate("end_date"));
                borrowBooks.setReturnDate(rs.getDate("return_date"));
                borrowBooks.setIllegal(rs.getString("illegal"));
                borrowBooks.setUserId(userId);
                borrowBooks.setBookName(rs.getString("bookName"));
                list.add(borrowBooks);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return list;
    }

    public static int getBorrowBooksCountByUserId(int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_GET_BORROW_BOOKS_COUNT_BY_USERID);
            state.setInt(1,userId);
            rs = state.executeQuery();
            if (rs.next()) {
                return rs.getInt("count");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return 0;
    }

    public static int getHistoryBorrowBooksCountByUserId(int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            state = conn.prepareStatement(SQL_GET_HISTORY_BORROW_BOOKS_COUNT_BY_USERID);
            state.setInt(1,userId);
            rs = state.executeQuery();
            if (rs.next()) {
                return rs.getInt("count");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,rs);
        }
        return 0;
    }

    public static int insertBorrowBook(BorrowBooks b) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        int i = 0;
        try {
            state = conn.prepareStatement(SQL_INSERT_BORROW_BOOK);
            state.setInt(1, b.getBookId());
            state.setDate(2, new java.sql.Date(b.getBorrowDate().getTime()));
            state.setDate(3, new java.sql.Date(b.getEndDate().getTime()));
            state.setInt(4, b.getUserId());
            i= state.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return i;
    }

    public static int removeBorrowBook(int bookId, int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        try {
            state=conn.prepareStatement(SQL_REMOVE_BORROW_BOOK_BY_USERID_AND_BOOKID);
            state.setInt(1, bookId);
            state.setInt(2, userId);
            int i = state.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return 0;
    }

    public static int updateBorrowBook(String illegal,int bookId, int userId) {
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state=null;
        try {
            state = conn.prepareStatement(SQL_RETURN_BORROW_BOOK_BY_USERID_AND_BOOKID);
            state.setDate(1,new java.sql.Date(new java.util.Date().getTime()));
            state.setString(2,illegal);
            state.setInt(3, bookId);
            state.setInt(4, userId);
            int i = state.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return 0;
    }
}
